var express = require('express');
var router = express.Router();
var fs = require('fs');
var path = require('path');
// 导入MySQL模块
var mysql = require('mysql');
var cateSQL = require('../conf/catesql');
var pool = mysql.createPool(require('../conf/dbconf'));
//导入uuid
var uuid = require('node-uuid');
//导入os模块
var os=require('os');
// 导入excel处理模块
// 导入子程序处理模块
var exec = require('child_process').exec;
var xlsx=require('node-xlsx');
//导入、配置文件上传组件
var multer = require('multer');
var schedule =  require('node-schedule');
var storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, './public/upload')
  },
  filename: function (req, file, cb) {
    cb(null,file.originalname);
  }
});
var upload = multer({ storage: storage });
///////////////////////教师模块///////////////////////////////////
/**
 * 教师登录
 */
router.post('/login',(req,res)=>{
  // 入参判断
  if(req.body.username==undefined) return res.json({msg:'param is empty'});
  if(req.body.password==undefined) return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    if(err) res.json({msg:err});
    connection.query('SELECT * FROM Tea_Massage_Table WHERE Login_Name=? and Login_PassWord=?',[req.body.username,req.body.password],(err,resutls)=>{
      if (err) res.json({msg:err});
      if(resutls.length>0){
        // 账户正常 写入session
        if(resutls[0].status==1){req.session.userid=resutls[0].tea_id;res.cookie('username',resutls[0].login_name,{maxAge:1000*60*60*24*31, httpOnly: true });res.json({status:resutls[0].status,userid:resutls[0].tea_id});}
        // 账户禁用 写入session
        if(resutls[0].status==2){req.session.userid=resutls[0].tea_id;res.json({status:resutls[0].status,userid:resutls[0].tea_id});}
      }else res.json({status:404,msg:'user not exist'});
    });
  connection.release();
  });
});
/**
 * 教师注册
 */
router.post('/reg',(req,res)=>{
  // 入参判断
  if(req.body.tea_name==undefined) return res.json({msg:'param is empty'});
  if(req.body.login_name==undefined) return res.json({msg:'param is empty'});
  if(req.body.login_pwd==undefined) return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    // 判断login_name是否被占用
    connection.query('SELECT * FROM Tea_Massage_Table WHERE Login_Name=?',req.body.login_name,(err,results)=>{
      if(err) throw err;
      // 没有占用
      if(results.length==0){
        let regsql='INSERT INTO Tea_Massage_Table(stu_name,login_name,login_pwd) VALUES(?,?,?,?)';
        connection.query(regsql,[req.body.stu_name,req.body.login_name,req.body.login_pwd],(err,resutls)=>{
          if (err) throw err;
          if(resutls.affectedRows>0){
            res.json({msg:'reg success'});
          }else{
            res.json({msg:'reg fail'});
          }
        });
      }else{
        // 被注册
        res.json({msg:'user is exist'})
      }
    });
    connection.release();  
  });
});
/**
 * 用户修改密码
 * @method POST
 * @param {userid: "zhangsan", oldpwd: "123", newpwd: "456"}:Object
 * @returns 1.成功：{code:200,msg:'修改成功'}:Object 2.失败：{code:300,msg:'密码错误'}:Object
 */
router.post('/updatePwd',(req,res)=>{
  // 入参判断
  pool.getConnection((err,connection)=>{
    // 判断Login_Name、Login_PassWord是否正确并更新密码
    let updateSql='UPDATE Tea_Massage_Table SET Login_PassWord=? WHERE Login_Name=? AND Login_PassWord=?'
    connection.query(updateSql,[req.body.newpwd,req.body.userid,req.body.oldpwd],(err,results)=>{
      if(err) throw err;
      if(results.affectedRows>0) res.json({code:200,msg:'修改成功'});
      else res.json({code:300,msg:'密码错误'});
    });
    connection.release();
  });
});
/**
 * 获取教师信息
 */
router.get('/info',(req,res)=>{
  // 入参判断
  if(req.query.login_name==undefined) return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    connection.query('SELECT * FROM Tea_Massage_Table WHERE Login_Name=?',req.query.login_name,(err,resutls)=>{
        if (err) throw err;
        if(resutls.length>0){
          res.json(resutls);
        }else res.json({msg:'user not exist'});
    });
    connection.release();
  }); 
});
///////////////////////学生管理模块///////////////////////////////////
/**
 * 按页获取用户信息
 */
router.get('/listUserByPage', function (req, res) {
  // 入参判断
  if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
  if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(login_name) AS COUNT FROM student', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
      var sql = 'SELECT s.login_name,s.login_pwd,s.stu_name,c.cls_name,c.cls_start_time FROM student s LEFT JOIN class_info c ON(s.cls_id=c.cls_id) limit ?,?';
      connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
        if (err) throw err;
        res.json({ code:0, msg: "", count: count, data: results});
      });
    });
    connection.release();
  });
});

/**
 * 查找学生
 */
router.get('/findUser', function (req, res) {
  // 入参判断
  if(req.query.stu_name==undefined||req.query.stu_name=='') return res.json({msg:'param is empty'});
  pool.getConnection((err, connection) => {
    if (err) throw err;
    let sql='SELECT s.login_name,s.login_pwd,s.stu_name,c.cls_name,c.cls_start_time FROM student s LEFT JOIN class_info c ON(s.cls_id=c.cls_id) WHERE stu_name LIKE ?';
    connection.query(sql,'%'+req.query.stu_name+'%', (err, results) => {
      if (err) throw err;
      res.json(results);

    });
    connection.release();
  });
});
/**
 * 删除学生
 */
router.get('/delUser', function (req, res) {
  // 入参判断
  if(req.query.login_name==undefined||req.query.login_name=='') return res.json({msg:'param is empty'});
  pool.getConnection((err, connection)=>{
    if (err) throw err;
    connection.query('DELETE FROM student WHERE login_name=?',req.query.login_name,(err, results) => {
      if (err) throw err;
      if(results.affectedRows>0) res.json({code:200,results:results});
      else res.json({code:300,results:results});
    });
    connection.release();
  });
});
///////////////////分类模块///////////////////////
//增加分类
// router.get('/addcate', function (req, res) {
//   pool.getConnection((err, connection) => {
//     var parms = [req.query.name, imagename.length!=0?imagename[0]:'', req.query.note, req.query.belong];
//     connection.query('insert into cate(name,pic,note,belong) values(?,?,?,?)', parms, (err, resutls) => {
//       if (err) throw err;
//       if (resutls.affectedRows == 1) res.json(1);
//       else res.json(0);
//     });
//     connection.release();
//   });
// });
// //删除分类
// router.get('/delcate', function (req, res) {
//   pool.getConnection((err, connection) => {
//     if (err) throw err;
//     connection.query('delete from cate where id=?', req.query.id, (err, results) => {
//       if (err) throw err;
//       if (results.affectedRows > 0) {
//         res.json(1)
//       } else {
//         res.json(0);
//       }
//     });
//     connection.release();
//   });
// });
// //修改分类
// router.get('/editcate', function (req, res) {
//   pool.getConnection((err, connection) => {
//     if (err) throw err;
//     var parms = [req.query.name, req.query.pic, req.query.note, req.query.belong, req.query.id];
//     connection.query('update cate set name=?,pic=?,note=?,belong=? where id=?', parms, (err, results) => {
//       if (err) throw err;
//       if (results.affectedRows > 0) {
//         res.json(1)
//       } else {
//         res.json(0);
//       }
//     });
//     connection.release();
//   });
// });
// //查找分类byID
// router.get('/findcate', function (req, res) {
//   pool.getConnection((err, connection) => {
//     if (err) throw err;
//     connection.query('select * from cate where id=?', req.query.id, (err, results) => {
//       if (err) throw err;
//       res.json(results);
//     });
//     connection.release();
//   });
// });
//查找分类byBelong
// router.get('/findParentCate', function (req, res) {
//   pool.getConnection((err, connection) => {
//     if (err) throw err;
//     connection.query('select * from cate where belong=?', req.query.id, (err, results) => {
//       if (err) throw err;
//       res.json(results);
//     });
//     connection.release();
//   });
// });
// //查找所有分类
// router.get('/listcate', function (req, res) {
//   var page = parseInt(req.query.page);
//   var limit = parseInt(req.query.limit);
//   pool.getConnection((err, connection) => {
//     var count;
//     if (err) throw err;
//     connection.query('SELECT COUNT(id) AS COUNT FROM cate', (err, results) => {
//       if (err) throw err;
//       count = results[0].COUNT;
//     });
//     var sql = 'SELECT c1.id,c1.`name`,c1.pic,c1.note,c2.id as parent_id,c2.`name` as parent_name FROM cate c1 LEFT JOIN cate c2 ON (c1.belong=c2.id) ORDER BY c1.id limit ?,?';
//     connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
//       if (err) throw err;
//       res.json({ "code": 0, "msg": "", "count": count, "data": results });
//     });
//     connection.release();
//   });
// });

//分类图片上传
var imagename = [];
router.post("/uploadImgs",upload.any(), function (req, res) {
  imagename.splice(0,imagename.length);       // 1.先清空数组 imagename=[]
  imagename.push(req.files[0].originalname);  // 2.将文件名压入数组
  res.json({ code: 200});
});

////////////////////////////考勤相关模块/////////////////////////


// 考勤历史记录
router.get('/listSignInRecord', (req, res) => {
  // 入参判断
  if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
  if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
  var page = parseInt(req.query.page);
  console.log(req);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(id) AS COUNT FROM sign_in', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
    });
    var sql ='SELECT s.id,u.stu_name,s.node,s.time,s.title FROM sign_in s LEFT JOIN student u ON(s.userid=u.login_name) limit ?,?';
    connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": count, "data": results });
    });
    connection.release();
  });
});

// 显示课程评分列表
router.get('/listAllCourseScore', (req, res) => {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('select * from course_score', (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": '0', "data": results });
    });
    connection.release();
  });
});

/**
 * 自动数据后台显示
 */
router.get('/listRecordByPage', (req, res) => {
  // 入参判断
  if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
  if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(app_his_id) AS COUNT FROM autodata', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
      var sql = 'SELECT a.app_his_id,s.stu_name,a.call_time,a.net_flow,a.usephone_time,a.sleep_time,a.step_count,a.music_time,a.date FROM autodata a LEFT JOIN student s ON(a.userid=s.login_name) limit ?,?';
      connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
        if (err) throw err;
        res.json({ code:0, msg: "", count: count, data: results});
      });
    });
    connection.release();
  });
});
/**
 * 删除自动数据
 */
router.get('/delRecord', (req, res) => {
  // 入参判断
  if(req.query.app_his_id==undefined||req.query.app_his_id=='') return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    if(err) return res.json({code:404,msg:'not connected'});
    connection.query('DELETE FROM autodata WHERE app_his_id=?',req.query.app_his_id,(err,results)=>{
      if(err) res.json({code:400,msg:'sql error'});
      if(results.affectedRows>0) res.json({code:200,msg:results});
      else res.json({code:300,msg:'del fail'});
    });
    connection.release();
  });
});
// 饮料词条建议后台显示
router.get('/drinkadjust', (req, res) => {
  // 入参判断
  if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
  if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(drink_ratio_id) AS COUNT FROM drink_ratio_healthy', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
    });
    var sql ='SELECT d.drink_ratio_id,c.cate_name,d.drink_ratio_num,d.drink_radio_desc,d.drink_ratio_comments from catetest c,drink_ratio_healthy d where c.cate_id=d.dr_cate_id  limit ?,?';
    connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": count, "data": results });
    });
    connection.release();
  });
});
// 添加饮料词条信息
router.get('/addadjust', function (req, res) {
  pool.getConnection((err, connection) => {
    console.log(req.query.belong)
    var parm=[req.query.belong,req.query.catename]
    connection.query('insert into catetest (parent_id,cate_name) values(?,?)', parm,(err,results)=>{
      if (err) throw err;
      if (results.affectedRows == 1) res.json(1);
      else res.json(0);
  })
    connection.release();
  });
});
//查找分类饮料父级
router.get('/findDrinkCate', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('SELECT cate_name,cate_id FROM `catetest` ORDER BY cate_id LIMIT 6', (err, results) => {
      if (err) throw err;
      res.json(results);
    });
    connection.release();
  });
});
/**
 * 获取系统信息
 */
router.get('/sysInfo',(req,res)=>{
  var sysInfo=[];
  sysInfo.push(os.arch());
  sysInfo.push(os.cpus().length);
  sysInfo.push(os.freemem());
  sysInfo.push(os.totalmem());
  sysInfo.push(process.version);
  sysInfo.push(os.type());
  sysInfo.push(os.uptime());
  sysInfo.push(__dirname);
  res.json(sysInfo);
});

/**
 * 版本对比
 */
router.get('/updateversion',(req,res)=>{
  //本机版本
  var oldVersion = req.query.versionCode;
  pool.getConnection(function(err,connection){
    if(err)throw err;
    connection.query('select versionCode,versionName,versionDescript,apkUrl from apkversion order by id desc limit 1',function(err,result){
        if(err) throw err;
        //本机版本与最新版本对比
        if(oldVersion<result[0].versionCode){
          //更新新版本
          res.json(result);
        }else{
          //不更新版本
          res.json(null);
        }
        
    });
    connection.release();
  });
});
/**
 * 下载新版本
 */
router.get('/downapk',(req,res)=>{
  //获取下载版本的路径
  var apkUrl = req.query.apkUrl;
  //拼接路径
  var rootUrl = path.resolve(__dirname,'..');
  var url = rootUrl+"/public/downloadapk/version/"+apkUrl;
  var f = fs.createReadStream(url);
  res.writeHead(200,{'Content-Type':'application/force-download','Content-Disposition':'attachment; filename='+apkUrl});
  //下载
  f.pipe(res);
});
////////////////////学生表操作///////////////////////////////////////
// 保存客户端的学生表
router.get('/importStudentsTable',(req,res)=>{
  var filepath=path.resolve(__dirname,'..')+'/public/upload/1.xlsx';
  var workSheetsFromFile=xlsx.parse(filepath);//解析xlsx文件
  var studata=workSheetsFromFile[1].data;//获取工作表0中数据
  console.log(studata);
  return
  studata.splice(0,2);//删除表格标题和表头
  var sql='INSERT INTO student(id,name,sex,birthday,student_id) VALUES ';
  // 为每条记录增加uuid，拼接sql语句
  for(var i=0;i<studata.length;i++){
    (studata[i][2]=='男')?studata[i][2]=1:studata[i][2]=0;//数据格式转换
    studata[i].push(uuid.v4());//增加每条记录的uuid
    sql+='(\''+studata[i][0]+'\',\''+studata[i][1]+'\',\''+studata[i][2]+'\',\''+studata[i][3]+'\',\''+studata[i][4]+'\'),';
  }
  sql=sql.slice(0,-1);//去掉最后多余的逗号
  // 插入记录到数据库
  console.log(workSheetsFromFile);
  // return
  pool.getConnection((err,connection)=>{
    if(err) throw err;
      connection.query(sql,(err,results)=>{
        if(err) throw err;
        res.json({record:results.affectedRows});
    });
    connection.release();
  });
});

////////////////////课程表操作///////////////////////////////////////
// 保存客户端的课程表
router.post('/saveStudentTable',(req,res)=>{
  var courseTable=req.body.courseTable;
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    connection.query('sql',(err,results)=>{
      if(err) throw err;
      res.json(results);
    });
    connection.release();
  });
});
// 绝地求生后台展示
router.get('/pubgmanger',function(req,res){
   // 入参判断
   if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
   if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
   var page = parseInt(req.query.page);
   var limit = parseInt(req.query.limit);
   pool.getConnection((err, connection) => {
     var count;
     if (err) throw err;
     connection.query('SELECT COUNT(sorts) AS COUNT FROM pubg_percentage', (err, results) => {
       if (err) throw err;
       count = results[0].COUNT;
     });
     var sql ='SELECT * from pubg_percentage ORDER BY game_skill  limit ?,?';
     connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
       if (err) throw err;
       res.json({ "code": 0, "msg": "", "count": count, "data": results });
     });
     connection.release();
   });
})
// //删除pubg分类
router.get('/delpubg', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('delete from pubg_percentage where sorts=?', req.query.id, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
// 通知渲染动态渲染select1
router.get('/pubgselect', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('SELECT * FROM `pubgskill_name` ORDER BY sorts ',(err, results) => {
      if (err) throw err;
      res.json(results);    
    });
    connection.release();
  });
});
// pubg后台添加
router.get('/pubgadd', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    var parm=[req.query.game_skill,req.query.game_levels,req.query.game_adjust,req.query.game_comments];
    console.log(parm);
    connection.query('INSERT INTO pubg_percentage(game_skill,game_levels,game_adjust,game_comments) VALUES(?,?,?,?)',parm,(err, results) => {
      if (err) throw err;
      res.json(1);    
    });
    connection.release();
  });
});
// pubg后台编辑
router.get('/pubgedit', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    var parm=[req.query.game_skill,req.query.game_levels,req.query.game_adjust,req.query.game_comments,req.query.sorts];
    console.log(parm);
    connection.query('update pubg_percentage SET game_skill=?,game_levels=?,game_adjust=?,game_comments=? where sorts=?', parm, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
///////////////////////////////////LUA英雄联盟（腾讯）模块//////////////////////

/**
 * 接受lua请求，查找用户表中的数据对应的QQ与密码写回lua
 */
router.get("/luasearch",function(req,res){
  pool.getConnection((err,connection)=>{
    connection.query("select b.stu_id,b.qqnum,b.qqpwd from req_lol r left outer join bind_lol b on r.stu_id=b.stu_id where r.status=0 limit 1",(err,results)=>{
      if(err) throw err;
      //将数据库的状态设置为1,查到数据以后将此删除
      connection.query("update req_lol set status = 1 where stu_id =?;",[results[0].stu_id],(err,result)=>{
          console.log("修改分发的数据状态为1");
      });
      //数据写回浏览器
      str = {"qqnum":results[0].qqnum,"qqpwd":results[0].qqpwd};
      res.json(str);

    });
    connection.release();
  });
});


/**
 * 轮询地址，获取主机状态(暂时使用，后期用上面luasearch方法)
 * @param status 客户机状态
 */ 
var index=0;
router.get('/getdata',(req,res)=>{
  console.log(req.query.devstatus);
  //生成一个查询账号
  queryAccount=[{qqnum:'1919597949',qqpws:'cl1314520@'},{qqnum:'672800776',qqpws:'y20120816.'}];
  res.json(queryAccount[index]);
  console.log(index);
  index++;
});
/**
 * lua图片上传，生成python分析结果（d1，d2,...d7）
 */
router.post("/uploadpic",upload.any(), function (req, res) {
  // 自定义文件路径
  
  var rootUrl = path.resolve(__dirname,'..');
  var imagePath=rootUrl+'/public/upload/'+req.files[0].originalname;
  
  // 通过子线程执行
  exec('python getAbilityRatio.py '+imagePath,(error,stdout,stderr)=>{
    if(error) console.log(error);
    if(stdout.length >1){
      var abilityPoint=stdout.split('\\r\\n').join('').split(' ');
      pool.getConnection((err,connection)=>{
        if(err)throw err;
        var insertsql='INSERT INTO lol_radar VALUES(?,?,?,?,?,?,?,?,?)';
        //主键uuid
        abilityPoint.splice(0,0,uuid.v4());
        // 用户qqnum
        var qqnumstr = JSON.stringify(req.files[0].originalname);
        var num = qqnumstr.indexOf("lbx.png");
        var qq = qqnumstr.substring(1,num);
        abilityPoint.push(qq);
        connection.query(insertsql,abilityPoint,function(err,result){
          if(err) throw err;
          console.log(result);
         });
        connection.release();
      });
    }
    if(stderr) console.log(stderr); 
   
  });
});

/**
 * 系统定时任务(每周日的23:55开始把数据库导入)
 */
var j = schedule.scheduleJob({hour: 12, minute: 58, dayOfWeek: 4}, function(){
  
});

/**
 * 首页数据采集页面展示
 */
router.get('/first',function(req,res){
  pool.getConnection((err,connection)=>{
    if(err) throw err;
   
    //1.查找分类描述

    connection.query('select c.cate_id,c.cate_name,cd.cate_property_value from category c left outer join category_desc cd on c.cate_id=cd.cate_id',function(err,results){
      if(err) throw err;
      console.log(results);
      var cate_arr = [];
      results.forEach(function(v,i,a){
        a = v.cate_id;
        if(v.cate_id==a){

        }
      });
    });
    //2.查找分类的描述
    connection.query('select sp_id,cate_id,sp_key,sp_value from spec order by sp_sort',function(err,results){
      if(err) throw err;
      
    });
    //3.查找分类的选项
    connection.query('select dr_cate_id,dr_cate_name,cate_id from drink_cate',function(err,results){
      if(err) throw err;
      
    });
    //4.组织json串

  });
});

/**
 * 数据采集菜单
 */
router.get("/menu",function(req,res){
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    connection.query("select cate_id,cate_name,parent_id from catetest where cate_state=1 order by cate_sort",function(err,results){
      if(err) throw err;
      var n;
      var p = 0;
      var q = 0;
      rootArr = [];//根菜单
      results.forEach(function(v,i,a){
        if(v.parent_id==0){
          rootArr[i]=new menu(v.cate_id,v.cate_name,v.parent_id,[]);
        }else{
          
          secArr=[];//二级菜单
          
          rootArr.forEach(function(x,y,z){
            if(q==0){
              //记录上一次一级目录
              n = x.cate_id;
              if(v.parent_id==x.cate_id){//进此循环者，属二级菜单项
                rootArr[y].childList[p]=new menu(v.cate_id,v.cate_name,v.parent_id,[]);
              }
              q=q+1;
            }else{
              if(x.cate_id==v.parent_id){//进此循环者，属二级菜单项
                if(v.parent_id==n){
                  p=p+1;
                }else{
                  p = 0;
                  n=v.parent_id;
                }
                rootArr[y].childList[p]=new menu(v.cate_id,v.cate_name,v.parent_id,[]);
               };
            }
        });
        }
      });
      //三级菜单的组织
      var n = 0;
      var m = 0;
      var j = 0;

      results.forEach(function(p,q,o){//所有数据
        rootArr.forEach(function(v,i,a){//一级菜单
          v.childList.forEach(function(x,y,z){//二级菜单的childList
            if(p.parent_id==x.cate_id){//三级菜单
              console.log("");
              if(j=0){
                //记录上次的cate_id
                n = x.cate_id;
                j=j+1;
              }
              if(p.parent_id==n){
                m=m+1;
              }else{
                m=0;
                n=p.parent_id;
              }
              rootArr[i].childList[y].childList[m]=new menu(p.cate_id,p.cate_name,p.parent_id,[]);
            }
          });
        });
      });
      res.json(rootArr); 
    })

    //构造方法构建一个菜单对象
    function menu(cate_id,cate_name,parent_id,childList){
      //四个属性
      this.cate_id=cate_id;
      this.cate_name=cate_name;
      this.parent_id=parent_id;
      this.childList = childList;
    };

  });
});

/**
 * 采集用户手动提交的数据
 */
router.post("/submitda",function(req,res){
  //1.获取内容和量对应的数组
  var desc = req.body.cate_desc;
  var spec = req.body.spec;
  //2.获取用户的id
  if(req.session.userid==undefined||req.session.userid=='') return res.json({msg:'param is empty'});
  var sid = req.session.userid;
  //3.按类组织数据为数组
  desc.forEach(function(v,i,a){
    var data = [spec[i],v,sid,v];
    var data2 = [v,sid];
    var data3 = [spec[i],v,sid];
  //4.保存数据到数据库
    var selectDrink = cateSQL.selectDrink;
    var insertDrink = cateSQL.insertDrink;
    var updateDrink = cateSQL.updateDrink;

    var selectTrain = cateSQL.selectTrain;
    var insertTrain = cateSQL.insertTrain;
    var updateTrain = cateSQL.updateTrain;
    if(i==0){
    
    }else if(i==1){
      pool.getConnection((err,connection)=>{
        if(err) throw err;
        connection.query(selectTrain,data2,function(err,results){
          console.log("i为1"+results.length+data2);
          if(results.length==1){
            connection.query(updateTrain,data3,function(err,results){
              console.log("更新");
              res.redirect('mindex.html');
            });
          }else{
            connection.query(insertTrain,data,function(err,results){
              console.log("插入");
              res.redirect('mindex.html');
            });
          }
        });
        connection.release();
      });
    }
  });
});
/**
 * 饮料提示的词条
 */
router.get("/catedes",function(req,res){
  //分类id
  var cate_id = req.query.cate_id;
  console.log(cate_id);
  //用户id
  //stu_id = req.session.userid;
  var stu_id = req.cookies.username;
  var select1;
  var select2=cateSQL.selectCateName;
  var select3;

  var sec_cate_id;
  var three_radio_desc;

  if(cate_id==1){
    select1=cateSQL.selectDrinkData;
    select3=cateSQL.selectDrinkDesc;
  }else if(cate_id==2){
    select1=cateSQL.selectTrainData;
    select3=cateSQL.selectTrainDesc;
  }
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    console.log("查询开始");
    connection.query(select1,stu_id,function(err,result){
      if(err) throw err;
      var len = result.length;
      //获取随机1,length的随机整数，应该前闭后开[0,length)。
      var a = Math.floor(Math.random()*len);
      //获取了分类id,和对应的数值
      if(cate_id==1){
       sec_cate_id = result[a].dr_cate_id;
      } else if(cate_id==2){
        sec_cate_id = result[a].tra_cate_id;
      }
      
      var sj_num = result[a].sj_num;
      var name;
      
      connection.query(select2,[sec_cate_id],function(err,results){
        console.log("======");
        console.log(select2);
        console.log(results);
        name = results[0].cate_name;
      });
      connection.query(select3,[sec_cate_id,sj_num],function(err,results){
        if(err) throw err;
        if(cate_id==1){
          if(results.length>0){
            res.json(name+"建议:"+results[0].drink_radio_desc);
          }else{
            res.json(name+"建议:采集数据过少，请多提交。");
          }
        }else if(cate_id==2){
          if(results.length>0){
            res.json(name+"建议:"+results[0].sp_radio_desc);
          }else{
            res.json(name+"建议:采集数据过少，请多提交。");
          }
        }
        
      });
    })
    connection.release();
  });
});

//--------- 通知相关功能开始--------------
/**
 * 发送通知页面
 */
router.get("/notify",function(req,res){
  //1. 获取通知的类别，范围，标题，内容，发送者
    var msg_type = req.query.msg_type;
    if(msg_type==null){
      msg_type = 2;
    }
    var msg_range = req.query.msg_range;
    if(msg_range==null){
       msg_range = 4;//默认是4，即为班级通知
    }
    var msg_title = req.query.msg_title;
    var msg_content = req.query.msg_content;
    var msg_author = req.query.msg_author;
    if(msg_author==null){
      msg_author="班主任";
    }
    var Msg_Id;//插入的通知id
  pool.getConnection((err,connection)=>{
  //2. 插入通知表
    connection.query("insert into Msg_Content_Table(Msg_Type,Msg_Range_Id,Msg_Title,Msg_Content,Msg_Author) values(?,?,?,?,?)",[msg_type,msg_range,msg_title,msg_content,msg_author],function(err,results){
      if(err) throw err;
      Msg_Id = results.insertId;
  //3.插入读取表
        //3.1 判断范围，得到要接受通知者（1.全校，2.老师，3.学生, 4.班级通知）
          var selectSQL;
          var selectData = [Msg_Id,0];
          console.log("id是"+Msg_Id);
          if(msg_range==1){
            selectSQL="insert into Not_User_Table(Msg_Id,Status,User_Id) "+
            "select ?,?,t.Tea_Id  from Tea_Massage_Table t union "+
            " select ?,?,s.Stu_Id from Stu_Massage_Table s";
            selectData = [Msg_Id,0,Msg_Id,0];
          }else if(msg_range==2){
            selectSQL="insert into Not_User_Table(Msg_Id,Status,User_Id) select ?,?,Tea_id from Tea_Massage_Table";
          }else if(msg_range==3){
            selectSQL="insert into Not_User_Table(Msg_Id,Status,User_Id) select ?,?,Stu_Id from Stu_Massage_Table";
          }else if(msg_range==4){
            console.log("进入判断");
            //var tea_id = req.session.userid;
            var tea_id = req.cookies.username;
            selectSQL="insert into Not_User_Table(Msg_Id,Status,User_Id) "+
            " select ?,?,Stu_Id from Stu_Massage_Table where Cls_Id= "+
            " (select Cls_Id from Cls_Massage_Table where Tea_Id=?)";
            selectData = [Msg_Id,0,tea_id];
          }
        //3.2 插入接受表
        connection.query(selectSQL,selectData,function(err,results){
          if(err) throw err;
          console.log(results);
        });
        connection.release();
        res.json(1);
      });
    });
});
// //删除通知分类
router.get('/delnotice', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('delete from Msg_Content_Table where Msg_id = ?', req.query.id, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
/**
 * 查询未读通知数目(未写完)
 */
router.get("/notifycount",function(req,res){
  //1. 获取分类id
    var msg_type = req.query.msg_type;
  //2. 获取用户id
    var userid = req.session.userid;
  //3. 查询条数
  pool.getConnection((err,connection)=>{
    connection.query("select t1.Msg_Id from Msg_Content_Table as t1 left join Not_Student_Table as t2 on t2.Msg_Id=t1.Msg_Id where t2.Msg_Id is null and 1-t1.Msg_Range_Id<0",[stu_id],function(err,results){
      var arr = [];
      results.forEach(function(v,i){
        arr[i]=v.Msg_Id;
      });
      res.json(arr);
      connection.release();
    });
  });
});
// 通知渲染动态渲染select1
router.get('/noticeselects', function (req, res) {
  console.log(req);
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('SELECT * FROM `Msg_Type_Table` ORDER BY Msg_Type_Id ', req.query.id, (err, results) => {
      if (err) throw err;
      res.json(results);    
    });
    connection.release();
  });
});
// 数据库渲染动态渲染select2
router.get('/noticeselec', function (req, res) {
  console.log(req);
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('SELECT * FROM `Msg_Range_Table` ORDER BY Msg_Range_Id ', req.query.id, (err, results) => {
      if (err) throw err;
      res.json(results);    
    });
    connection.release();
  });
});
// 查找通知父级
router.get('/noticeparent', function (req, res) {
  pool.getConnection((err, connection) => {
    console.log(req);
    if (err) throw err;
    connection.query('select * from Msg_Content_Table where Msg_id = ?', req.query.id, (err, results) => {
      if (err) throw err;
      res.json(results);
      // connection.query('select * from Msg_Content_Table where Msg_id=?',sqlData,function(err,results){
      //   res.locals.results=results;
      //   console.log("结果是"+results);
        
      // });
    });
    connection.release();
  });
});
// 通知列表后台显示
router.get('/messagelist', (req, res) => {
  // 入参判断
  if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
  if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(Msg_Id) AS COUNT FROM Msg_Content_Table', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
    });
    var sql ='SELECT d.Msg_Id,d.Msg_Type,c.Range_Name,d.Msg_Content,d.Msg_Author,d.Msg_Title,d.Time_Send from Msg_Range_Table c,Msg_Content_Table d where c.Msg_Range_Id=d.Msg_Range_Id  ORDER BY Msg_Id  limit ?,?';
    connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": count, "data": results });
    });
    connection.release();
  });
});
//修改通知分类
router.get('/editnotice', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    var parms = [req.query.msg_title, req.query.msg_type, req.query.msg_range, req.query.msg_author, req.query.msg_content,req.query.Msg_Id];
    console.log(parms)
    connection.query('update Msg_Content_Table SET  Msg_Title=?,Msg_Type=?,Msg_Range_Id=?,Msg_Author=?,Msg_Content=? where Msg_Id=?', parms, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
/**
 * 查询通知列表(未读已读标志在内,按时间排序)
 */
router.get("/notifylist",function(req,res){
  //1. 获取通知类别id，用户id
    //var userid = req.session.userid;
    var userid = req.cookies.username;
    var msg_type = req.query.msg_type;
  //2.sql语句和数据(降序查询)
    var sql = "select m.*,n.Status from Not_User_Table n left outer join Msg_Content_Table m "+
              " on m.Msg_Id=n.Msg_Id where m.Msg_Type=? and n.User_Id=? order by m.Time_Send desc"
    var sqlData = [msg_type,userid];
  //3.查询
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    connection.query(sql,sqlData,function(err,results){
      res.locals.results=results;
      console.log("结果是"+results);
      //res.json(results);
      res.render("notice-list.html");
      connection.release();
    });
  });
});
/**
 * 查询通知详情
 */
router.get("/notifydetail",function(req,res){
  console.log("进入查询详情");
   //1. 获取用户id,通知id
    var userid = req.session.userid;
    var msg_id = req.query.msg_id;
  pool.getConnection((err,connection)=>{
    //2. 根据通知id查询通知
      var sql = "select * from Msg_Content_Table where Msg_Id=?";
      var sqlData = [msg_id];
      connection.query(sql,sqlData,function(err,results){
        if(err) throw err;
        res.locals.results=results;
        console.log(results);
    //3. 修改字段为已读
        var updateSql = "update Not_User_Table set Status=1 where Msg_Id=? and User_Id=?";
        var updateDate = [msg_id,userid];
        connection.query(updateSql,updateDate,function(err,results){
          console.log(results);
    //4. 携带数据跳转页面
          res.render("notice-detail.html");
        });
      });
      connection.release();
  });
});
//--------- 通知相关功能结束--------------

//----------计步相关功能开始--------------
// 计步词条建议列表后台显示
router.get('/walkadjust', (req, res) => {
  // 入参判断
  if(req.query.page==undefined||req.query.page=='') return res.json({msg:'param is empty'});
  if(req.query.limit==undefined||req.query.limit=='') return res.json({msg:'param is empty'});
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(walk_ratio_id) AS COUNT FROM walk_ratio_healthy', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
    });
    var sql ='SELECT * from walk_ratio_healthy ORDER BY wk_ratio_num  limit ?,?';
    connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": count, "data": results });
    });
    connection.release();
  });
});
// 增加计步建议
 router.get('/walkadd',(req,res)=>{
   pool.getConnection((err,connection)=>{
    if (err) throw err;
    var parm=[req.query.wk_ratio_num,req.query.wk_radio_desc,req.query.wk_ratio_comments]
    connection.query('INSERT INTO walk_ratio_healthy (wk_ratio_num,wk_radio_desc,wk_ratio_comments) VALUES (?,?,?)',parm,(err,results) =>{
      if (err) throw err;
      res.json(1);
    })
   })
 })
//  删除计步建议
router.get('/walkdel', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('delete from walk_ratio_healthy where walk_ratio_id = ?', req.query.id, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
//修改通知分类
router.get('/walkedit', function (req, res) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    var parms = [ req.query.wk_ratio_num, req.query.wk_radio_desc, req.query.wk_ratio_comments,req.query.walk_ratio_id];
    console.log(parms)
    connection.query('update walk_ratio_healthy SET wk_ratio_num=?,wk_radio_desc=?,wk_ratio_comments=? where walk_ratio_id=?', parms, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
/**
 * 8月6日改版后的数据提交
 */
router.get("/submitdata",function(req,res){
  
  //1.获取类型id，类型，量
    var cate_id = req.query.cate_id;
    var cate = req.query.cate;
    var spec = req.query.spec;
  //2.获取学生id
    //var sid = req.session.userid;
    var sid = req.cookies.username;
    console.log("登录用户是："+sid);
    //组织数据
    var selectdata = [cate,sid];
    var updatedata = [spec,cate,sid];
    var insertdata = [spec,cate,sid,cate];
  //3.分辨提交类别
    var select;
    var insert;
    var update;
    if(cate_id==1){
      var select = cateSQL.selectDrink;
      var insert = cateSQL.insertDrink;
      var update = cateSQL.updateDrink;
    }else if(cate_id==2){
      var select = cateSQL.selectTrain;
      var insert = cateSQL.insertTrain;
      var update = cateSQL.updateTrain;
    }

  //4.写往数据库
    pool.getConnection((err,connection)=>{
      if(err) throw err;
      connection.query(select,selectdata,function(err,results){
        if(results.length==1){
          connection.query(update,updatedata,function(err,results){
            console.log("更新");
          });
        }else{
          connection.query(insert,insertdata,function(err,results){
            console.log("插入");
          });
        }
      });
      connection.release();
    });
    
});

/**
 * 8月6日改版后查看建议
 */
router.get("/catedesc",function(req,res){
  //1. 获取分类id
    var cate_id = req.query.cate_id;
  //2. 获取用户id
    //var stu_id = req.session.userid;
    var stu_id = req.cookies.username;
    console.log("登录用户是:"+stu_id);
  //3. 查找分类名字
    var select1;
    var select2=cateSQL.selectCateName;
    var select3;
  //4. 获取二级分类id
    var sec_cate_id= req.query.sec_cate_id;

  //var sec_cate_id;      //id
  var sec_cate_data;    //数据
  var three_radio_desc; //描述
  console.log("接受到的数据"+cate_id+":"+sec_cate_id);
  if(cate_id==1){
    select1=cateSQL.selectDrinkData;
    select3=cateSQL.selectDrinkDesc;
  }else if(cate_id==2){
    select1=cateSQL.selectTrainData;
    select3=cateSQL.selectTrainDesc;
  }
  
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    console.log("查询开始");
    //5. 获取某一个分类的值
    connection.query(select1,[stu_id,sec_cate_id],function(err,result){
      if(err) throw err;
      var len = result.length;
      sec_cate_data =Math.floor(result[0].sj_num/7); 
      //获取了分类id,和对应的数值
      // if(cate_id==1){
      //  sec_cate_id = result[a].dr_cate_id;
      // } else if(cate_id==2){
      //   sec_cate_id = result[a].tra_cate_id;
      // }
      
      //var sj_num = result[a].sj_num;

      //6. 获取某一个分类的名字
      var name;
      connection.query(select2,[sec_cate_id],function(err,results){
        console.log(select2);
        console.log(results);
        name = results[0].cate_name;
      });
      //7. 获取某一个分类的建议词条
      connection.query(select3,[sec_cate_id,sec_cate_data],function(err,results){
        if(err) throw err;
        if(cate_id==1){
          if(results.length>0){
            //res.json(name+"建议:"+results[0].drink_radio_desc);
            res.json({"data":sec_cate_data,"desc":name+"建议:"+results[0].drink_radio_desc});
          }else{
            res.json({"data":sec_cate_data,"desc":name+"建议:饮用在合理范围。"});
          }
        }else if(cate_id==2){
          if(results.length>0){
            //res.json(name+"建议:"+results[0].sp_radio_desc);
            res.json({"data":sec_cate_data,"desc":name+"建议:"+results[0].sp_radio_desc});
          }else{
            //res.json(name+"建议:锻炼在合理范围。");
            res.json({"data":sec_cate_data,"desc":name+"建议:饮用在合理范围。"});
          }
        }
        
      });
    })
    connection.release();
  });
});
module.exports = router;